package service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import base.BaseService;

import pojo.MemberLogin;
import pojo.MemberInfor;






public class MemberService extends BaseService{
	       public class MemberRowMapper implements RowMapper<MemberLogin> {
	
	    	   public MemberLogin mapRow(ResultSet rs, int rownum) throws SQLException {
        
		MemberLogin inf = new MemberLogin();
		inf.setId(rs.getInt("id"));
		inf.setUsername(rs.getString("username"));
		inf.setPassword(rs.getString("password"));
		inf.setRegisterTime(rs.getString("registerTime"));
		
	
		return inf;
				}
	       }
//统计用户人数
	public int getALL(String where){
		String sql = "SELECT count(*) FROM memberLogin "+where+"";
		
		int count1 = this.jdbcTemplate.queryForInt(sql);
		
		return count1;
		
	}
//用户列表
	public List<MemberLogin> getMember(int pageno,int pagesize,String where){
			String sql = "SELECT * from memberLogin  "+where+" limit "+(pageno-1)*pagesize+","+pagesize;
			return jdbcTemplate.query(sql,new MemberRowMapper());
	}

//删除用户登陆信息
	 public int DeleteM(int id){
			
			String sql ="delete from memberLogin where id=?";
			
			return jdbcTemplate.update(sql,id);
			
	 }	
	 
//删除用户注册具体信息
	 
	 public void DeleteMinfor(int id){
		 
		 String sql ="delete from memberinfor where userid="+id;
		 
		 jdbcTemplate.update(sql);
		 
		 
	 }
//根据id获取用户信息
	 public MemberInfor getMemberByid(int id){
			class RowMapper implements org.springframework.jdbc.core.RowMapper<MemberInfor> {

				public MemberInfor mapRow(ResultSet rs, int rownum) throws SQLException {

					MemberInfor inf = new MemberInfor();
					inf.setUserid(rs.getInt("userid"));
					inf.setSex(rs.getInt("sex"));
					inf.setRealname(rs.getString("real_name"));
					inf.setIDcard(rs.getString("IDcard"));
					inf.setTel(rs.getString("tel"));
					inf.setEmail(rs.getString("email"));
					inf.setBirthday(rs.getString("birthday"));
					inf.setJob(rs.getString("job"));
					inf.setPermit(rs.getInt("permit"));
					
					
				
					return inf;
							}
					}
			
			String sql="select * from memberInfor where userid=?";
			MemberInfor inf=this.jdbcTemplate.queryForObject(sql, new RowMapper(),id);
			return inf;
		}
//根据用户名获取用户登陆信息
	 public MemberLogin getMember(String name){
			class RowMapper implements org.springframework.jdbc.core.RowMapper<MemberLogin> {

				public MemberLogin mapRow(ResultSet rs, int rownum) throws SQLException {

					MemberLogin inf = new MemberLogin();
					inf.setId(rs.getInt("id"));
					inf.setUsername(rs.getString("username"));
					inf.setPassword(rs.getString("password"));
					inf.setRegisterTime(rs.getString("registerTime"));					
					return inf;
							}
					}
			MemberLogin inf=null;
			String sql="select * from memberLogin where username=?";
			try{
			 inf=this.jdbcTemplate.queryForObject(sql, new RowMapper(),name);
			}catch(EmptyResultDataAccessException e){
				
			}
			return inf;
		}
//根据Id获取用户登陆信息
	 public MemberLogin getInfo(int id){
			class RowMapper implements org.springframework.jdbc.core.RowMapper<MemberLogin> {

				public MemberLogin mapRow(ResultSet rs, int rownum) throws SQLException {

					MemberLogin inf = new MemberLogin();
					inf.setId(rs.getInt("id"));
					inf.setUsername(rs.getString("username"));
					inf.setPassword(rs.getString("password"));
					inf.setRegisterTime(rs.getString("registerTime"));					
					return inf;
							}
					}			
			String sql="select * from memberLogin where id=?";
			MemberLogin user=this.jdbcTemplate.queryForObject(sql, new RowMapper(),id);
			return user;
			
		}
//更新用户信息
	 public void getUpdateByid(int sex,String realname,String IDcard,String tel,String email,String birthday,String job,int permit,int id){
			
			String sql ="update memberInfor set sex=?,real_name=?,IDcard=?,tel=?,email=?,birthday=?,job=?,permit=? where userid=?";
			
			 jdbcTemplate.update(sql,sex,realname,IDcard,tel,email,birthday,job,permit,id);
			}	
//用户登陆
	 public MemberLogin login(String username,String password){
			
			String sql = "select * from memberlogin where username=? and password=?";
			
			MemberLogin ml = null;
			
			try{
				ml = jdbcTemplate.queryForObject(sql, new MemberRowMapper(),username,password);
			
			}catch(EmptyResultDataAccessException e){
				
				}
			
			return ml;

			
		}
//用户注册，获取主键
	 public int register(final String username, final String password,final Timestamp registertime ){
		 PreparedStatementCreator psc = new PreparedStatementCreator() {
			String sql ="insert into memberlogin(username,password,registertime) values(?,?,?)";
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, username);
				ps.setString(2, password);
				ps.setTimestamp(3, registertime);
				return ps;
			}

		};
		KeyHolder key = saveandgetkey(psc);
		return key.getKey().intValue();
		 }
	 
	 
	 
	 public KeyHolder saveandgetkey(PreparedStatementCreator psc){
		 KeyHolder keyHolder =new  GeneratedKeyHolder();
		 jdbcTemplate.update(psc, keyHolder); 
		    return keyHolder;
		 
		     
	 }
//注册后在个人信息表生产id
	 public int add(int userid){
		 String sql ="insert into MemberInfor(userid,permit) values(?,0)";
			
			return jdbcTemplate.update(sql,userid);
		 
	 }
//根据用户名获取用户信息
	 public MemberInfor getMemberByUsername(String username){
			class RowMapper implements org.springframework.jdbc.core.RowMapper<MemberInfor> {

				public MemberInfor mapRow(ResultSet rs, int rownum) throws SQLException {

					MemberInfor inf = new MemberInfor();
					inf.setUserid(rs.getInt("userid"));
					inf.setPermit(rs.getInt("permit"));
					return inf;
							}
					}
	

		String sql="SELECT * from memberinfor JOIN memberlogin on memberlogin.id=memberinfor.userid where username=?";
		MemberInfor inf=this.jdbcTemplate.queryForObject(sql, new RowMapper(),username);
		return inf;
	}
//用户修改个人密码
	 public int updatePassword(String password,int id){
			
			String sql="update memberlogin set password=? where id=?";
			
			return jdbcTemplate.update(sql,password,id);
}

}
	
